# coding: utf-8
import datetime

import httpx
import pyodbc
from apscheduler.schedulers.blocking import BlockingScheduler


BASE_URL = "http://192.168.2.27:8006"
db_sql_server = (
    "DRIVER={SQL Server}",
    "SERVER=172.18.10.3,1433",
    "DATABASE=KFQSL",
    "UID=sa",
    "PWD=yckfq123!@#",
)

db_mysql = (
    "DRIVER={MySQL ODBC 8.0 Unicode Driver}",
    "SERVER=127.0.0.1",
    "DATABASE=rtudata",
    "USER=sorel",
    "PASSWORD=crea1002=",
)


def load_site() -> dict:
    site = {}
    device_names = set()
    device_types = set()
    device_addresses = set()
    for data in httpx.get(BASE_URL + "/dbDevice/getList").json()["data"]:
        if data["siteCode"] not in site:
            site[data["siteCode"]] = {}
            site[data["siteCode"]]["siteName"] = data["siteName"]  # site name
            site[data["siteCode"]]["devices"] = [data]
        elif site[data["siteCode"]]["siteName"] != data["siteName"]:  # duplicate site name
            print(f" - [ERROR] {data['siteName']} != {site[data['siteCode']]['siteName']}")
        site[data["siteCode"]]["devices"].append(data)
        device_names.add(data["deviceName"])
        device_types.add(data["deviceType"])
        device_addresses.add(data["deviceAddress"])
    print(f"- deviceName: {device_names}")
    print(f"- deviceType: {device_types}")
    print(f"- deviceAddress: {device_addresses}")
    return site


def post_data():
    """
    upLevel 室外、闸外、下游、外河
    lowLevel 室内、闸内、上游、内河

    """
    data = []  # the latest data of each device
    # query data from sql_server
    with pyodbc.connect(";".join(db_sql_server)) as conn:
        cursor = conn.cursor()
        # Step 1. query water level data
        sql = """
            SELECT * FROM dbo.T_Record_WaterLevel t1
            INNER JOIN (
                SELECT eq_id, MAX(collect_time) AS max_collect_time
                FROM dbo.T_Record_WaterLevel
                GROUP BY eq_id
            ) t2 ON t1.eq_id = t2.eq_id AND t1.collect_time = t2.max_collect_time; """
        cursor.execute(sql)
        items = cursor.fetchall()
        print(f"[WaterLevel] Found {len(items)} records.")
        for item in items:
            data.append(
                {
                    "uuid": item[1],
                    "time": item[2].strftime("%Y-%m-%d %H:%M:%S"),
                    "read": float(item[3]) if item[3] is not None else 0,
                }
            )
        # Step 2. query rain flow data
        sql = """
            SELECT * FROM dbo.T_Record_RainFall t1
            INNER JOIN (
                SELECT eq_id, MAX(collect_time) AS max_collect_time
                FROM dbo.T_Record_RainFall
                GROUP BY eq_id
            ) t2 ON t1.eq_id = t2.eq_id AND t1.collect_time = t2.max_collect_time; """
        cursor.execute(sql)
        items = cursor.fetchall()
        print(f"[RainFlow] Found {len(items)} records.")
        for item in items:
            data.append(
                {
                    "uuid": item[1],
                    "time": item[2].strftime("%Y-%m-%d %H:%M:%S"),
                    "read": float(item[3]) if item[3] is not None else 0,
                }
            )
    with pyodbc.connect(";".join(db_mysql)) as conn:
        # Step 3. query data from mysql
        cursor = conn.cursor()
        sql = """
            SELECT * FROM st_rsvr_r t1
            INNER JOIN (
                SELECT STCD, MAX(TM) AS max_TM
                FROM st_rsvr_r
                GROUP BY STCD
            ) t2 ON t1.STCD = t2.STCD AND t1.TM = t2.max_TM; """
        cursor.execute(sql)
        items = cursor.fetchall()
        print(f"[WaterRTU] Found {len(items)} records.")
        for item in items:
            data.append(
                {
                    "uuid": item[0],
                    "time": item[1].strftime("%Y-%m-%d %H:%M:%S"),
                    "read": float(item[2]) if item[3] is not None else 0,
                }
            )
    # Collect data for each site
    site_data = []
    for site_code, site in load_site().items():
        site_name = site["siteName"]
        totalRainData = None
        waterData = None
        upLevel = None
        lowLevel = None
        flowInfo = None
        time_str = "2020-01-01 00:00:00"
        for device in site["devices"]:
            eq_data = [item for item in data if item["uuid"] == device["deviceCode"]]
            if not eq_data:
                continue
            eq_data = eq_data[0]
            if device["deviceType"] == "water":
                if device["deviceAddress"] in ["室外", "闸外", "下游", "外河"]:
                    upLevel = eq_data["read"] if eq_data else None
                elif device["deviceAddress"] in ["室内", "闸内", "上游", "内河"]:
                    lowLevel = eq_data["read"] if eq_data else None
                else:
                    waterData = eq_data["read"] if eq_data else None
            elif device["deviceType"] == "rain":
                totalRainData = eq_data["read"] if eq_data else None
            elif device["deviceType"] == "flow":
                flowInfo = eq_data["read"] if eq_data else None
            else:
                continue
            if datetime.datetime.strptime(
                eq_data["time"], "%Y-%m-%d %H:%M:%S"
            ) > datetime.datetime.strptime(time_str, "%Y-%m-%d %H:%M:%S"):
                time_str = eq_data["time"]
        if time_str == "2020-01-01 00:00:00":
            continue
        site_data.append(
            {
                "reportTime": time_str,
                "readTime": time_str,
                "rainData": None,
                "waterData": waterData,
                "upLevel": upLevel,  # upLevel: 室外、闸外、下游、外河
                "lowLevel": lowLevel,  # lowLevel: 室内、闸内、上游、内河
                "totalRainData": totalRainData,
                "flowInfo": flowInfo,
                "voltage": "12.0",
                "siteCode": site_code,
            }
        )
        print(
            f"- [{site_data[-1]['readTime']}] {site_name}({site_data[-1]['siteCode']}) | totalRainData: {site_data[-1]['totalRainData']} | waterData: {site_data[-1]['waterData']} | upLevel: {site_data[-1]['upLevel']} | lowLevel: {site_data[-1]['lowLevel']} | flowInfo: {site_data[-1]['flowInfo']}"
        )
    # Post data to server
    now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(f"[{now_str}] Found {len(site_data)} records.")
    try:
        response = httpx.post(BASE_URL + "/dbDeviceHistory/add", json=site_data, timeout=5)
    except Exception as e:
        response = str(e)
    print(f"[{now_str}] {response}")


if __name__ == "__main__":
    post_data()
    scheduler = BlockingScheduler()
    scheduler.add_job(post_data, "interval", minutes=5)
    scheduler.start()
